/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package MODEL.DAO;

import MODEL.POJO.DanhMucPOJO;
import MODEL.POJO.LoaiDanhMucPOJO;
import MODEL.UTIL.MySqlDataAccessHelper;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author kijoo
 */
public class DanhMucDAO {

    public static ArrayList<DanhMucPOJO> LayDanhSachDanhMuc(String idloaidanhmuc) {
        ArrayList<DanhMucPOJO> ds = new ArrayList<DanhMucPOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            String sql = String.format("SELECT * FROM DANHMUC WHERE IDLOAIDANHMUC='%s'", idloaidanhmuc);
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                DanhMucPOJO dm = new DanhMucPOJO();
                dm.setIddanhmuc(rs.getString("iddanhmuc"));
                dm.setTendanhmuc(rs.getString("tendanhmuc"));
                ds.add(dm);
            }
            helper.Close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static int demsoluongdanhmuc(String iddanhmuc) {
        int soluong = 0;
        String sql = String.format("SELECT COUNT(*) FROM DANHMUC DM, SANPHAM SP, CHITIETSANPHAM CTSP"
                + "WHERE  DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND"
                + "DM.IDDANHMUC = '%s'", iddanhmuc);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soluong = rs.getInt("soluong");
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return soluong;
    }

    public static DanhMucPOJO LayTenDM(String iddanhmuc) {
        String sql = String.format("SELECT * FROM DANHMUC WHERE IDDANHMUC='%s' ", iddanhmuc);
        DanhMucPOJO dm= new DanhMucPOJO();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);            
            if (rs.next()) {                
                dm.setIddanhmuc(rs.getString("IDDANHMUC"));
                dm.setTendanhmuc(rs.getString("TENDANHMUC"));
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return dm;
    }
    
     public static DanhMucPOJO LayDMTheoSP(String idsanpham) {
        DanhMucPOJO dm= new DanhMucPOJO();
        String sql = String.format("SELECT DM.TENDANHMUC,DM.IDDANHMUC FROM DANHMUC DM,SANPHAM SP WHERE SP.IDSANPHAM = '%s' AND SP.IDDANHMUC = DM.IDDANHMUC ", idsanpham);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                dm.setTendanhmuc(rs.getString("TENDANHMUC"));
                dm.setIddanhmuc(rs.getString("IDDANHMUC"));
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return dm;
    }
    
    public static LoaiDanhMucPOJO LayTenLoaiDM(String iddanhmuc) {
        LoaiDanhMucPOJO ldm=new LoaiDanhMucPOJO();
        String sql = String.format("SELECT LDM.TENLOAIDANHMUC,LDM.IDLOAIDANHMUC FROM LOAIDANHMUC LDM,DANHMUC DM WHERE DM.IDDANHMUC='%s'AND DM.IDLOAIDANHMUC=LDM.IDLOAIDANHMUC ", iddanhmuc);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                ldm.setTenloaidanhmuc(rs.getString("TENLOAIDANHMUC"));
                ldm.setIdloaidanhmuc(rs.getString("IDLOAIDANHMUC"));
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return ldm;
    }
    
    public static String LayTenLoaiDMTheoSP(String idsanpham) {
        String ten = "";
        String sql = String.format("SELECT LDM.TENLOAIDANHMUC "
                + " FROM LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP "
                + " WHERE SP.IDSANPHAM='%s'AND SP.IDDANHMUC=DM.IDDANHMUC AND DM.IDLOAIDANHMUC=LDM.IDLOAIDANHMUC ", idsanpham);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                ten = rs.getString("TENLOAIDANHMUC");
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return ten;
    }
   
    public static boolean ThemDanhMuc(String maDM, String tenDM, String maLoaiDM) {
        boolean kq = false;
        try {
            String sql = String.format("INSERT INTO DANHMUC (IDDANHMUC,IDLOAIDANHMUC,TENDANHMUC) VALUES('%s','%s','%s')", maDM, maLoaiDM, tenDM);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            int n = helper.executeUpdate(sql);
            if (n == 1) {
                kq = true;
            }
            helper.Close();

        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return kq;

    }
}
